#Region "Imports"
Imports MySql.Data.MySqlClient 'For MySQL Components
#End Region
''' -----------------------------------------------------------------------------
''' Project	 : Server
''' Class	 : clsMySQL
''' 
''' -----------------------------------------------------------------------------
''' <summary>
''' Main class for MySQL connection and interaction
''' </summary>
''' <remarks>
''' Possibly add a SHA1 hash column to the `offlines` table
''' </remarks>
''' <history>
''' 	[Jason Schreckengost]	6/26/2006	Created
''' </history>
''' -----------------------------------------------------------------------------
Class clsMySQL
#Region "Variables"
    ''' -----------------------------------------------------------------------------
    ''' <summary>
    ''' The Connection to the MySQL Database Server
    ''' </summary>
    ''' <remarks>
    ''' When production happens, Database,User Id, and Password will change.
    ''' </remarks>
    ''' <history>
    ''' 	[Jason Schreckengost]	6/29/2006	Created
    ''' </history>
    ''' -----------------------------------------------------------------------------
    'Private MySQLCon As MySqlConnection = New MySqlConnection
    Private MySQLCon As MySqlConnection = New MySqlConnection("Server=localhost;Database=imtest;Persist Security Info=no;User Name=root;Password=comegetsome")
    ''' -----------------------------------------------------------------------------
    ''' <summary>
    ''' Where the MySQL Query will be stored
    ''' </summary>
    ''' <remarks>
    ''' </remarks>
    ''' <history>
    ''' 	[Jason Schreckengost]	6/29/2006	Created
    ''' </history>
    ''' -----------------------------------------------------------------------------
    Private MySQLCom As MySQLCommand = New MySQLCommand
    Const SyncRoot As String = "_"
    Private userId As Int32
    Private sBacklog As String
#End Region
#Region "Constructor/Destructors"
    ''' -----------------------------------------------------------------------------
    ''' <summary>
    ''' Class's Constructor
    ''' </summary>
    ''' <remarks>
    ''' Called during instantiation of class.
    ''' Simply opens the SQL connection
    ''' (Possibly think about non-persistant connection)
    ''' ^That would take too much time. The longest part of MySQL is the connection
    ''' </remarks>
    ''' <history>
    ''' 	[Jason Schreckengost]	6/29/2006	Created
    ''' </history>
    ''' -----------------------------------------------------------------------------
    Public Sub New(Optional ByVal id As Int32 = -1)

        'MySQLCon.ConnectionString = ""
        SyncLock SyncRoot
            If id > -1 Then userId = id
            Try
                'Why is this failing?
                MySQLCon.Open()
            Catch
                Display("MySQL Connection could not be established for " _
                + IIf(id = -1, "Main Program", "user #" + userId.ToString) _
                + vbNewLine + "Will retry on a background thread.")
                Dim thdSetMySQLCon As Threading.Thread = New Threading.Thread(AddressOf getMySQLCon)
                MySQLCon.Close()
                MySQLCon.Dispose()
                MySQLCon = Nothing
                thdSetMySQLCon.Start()
            End Try
        End SyncLock
    End Sub
    Protected Overrides Sub Finalize()
        '''TODO: Prob here with nullRef
        SyncLock SyncRoot
            If Not MySQLCom Is Nothing Then
                MySQLCom.Dispose()
                MySQLCom = Nothing
            End If
            If Not MySQLCon Is Nothing Then
                MySQLCon.Close()
                MySQLCon.Dispose()
                MySQLCon = Nothing
            End If
        End SyncLock
    End Sub
    Public Sub Close()
        Me.Finalize()
    End Sub
#End Region
#Region "Subroutines"
    ''' -----------------------------------------------------------------------------
    ''' <summary>
    ''' Sets user's UID (iUID in the instance)
    ''' </summary>
    ''' <param name="this">Reference to the current user</param>
    ''' <remarks>
    ''' "this" is a user from the Users() Array. See clsUser for more details
    ''' </remarks>
    ''' <history>
    ''' 	[Jason Schreckengost]	6/23/2006	Created
    ''' </history>
    ''' -----------------------------------------------------------------------------
    Public Sub setUserInfo(ByVal this As clsUser)
        If MySQLCon.Database <> "imtest" Then
            MySQLCon.ChangeDatabase("imtest")
        End If
        MySQLCom.CommandText = "SELECT UID FROM users where UN=?UN"
        MySQLCom.Prepare()
        MySQLCom.Parameters.Add("?UN", this.Username)
        MySQLCom.Connection = MySQLCon
        Dim myReader As MySqlDataReader
        myReader = MySQLCom.ExecuteReader()
        ' Always call Read before accessing data.
        While myReader.Read()
            this.UID = myReader.Item("UID")

        End While
        ' always call Close when done reading.
        myReader.Close()
    End Sub
    ''' -----------------------------------------------------------------------------
    ''' <summary>
    ''' Stores an Offline message into the database
    ''' </summary>
    ''' <param name="iTo">UID of recipient</param>
    ''' <param name="iFrom">UID of sender</param>
    ''' <param name="sText">Text (message) sent</param>
    ''' <remarks>
    ''' sText should be encrypted by this point
    ''' Maybe add sha1 message hash to DB
    ''' </remarks>
    ''' <history>
    ''' 	[Jason Schreckengost]	6/23/2006	Created
    ''' </history>
    ''' -----------------------------------------------------------------------------
    Public Sub setOffline(ByVal iTo As Integer, _
      ByVal iFrom As Integer, ByVal sText As String)
        Dim re As System.Text.RegularExpressions.Regex = New System.Text.RegularExpressions.Regex("'")
        sText = re.Replace(sText, "\\", "\\")
        sText = re.Replace(sText, "\'", "\'")
        Dim command As String = "INSERT INTO `offlines`(`to`,`from`,`text`,`time`) VALUES ('" + iTo.ToString + "','" + iFrom.ToString + "','" + sText + "',NOW())"
        MySQLCommand(command)
    End Sub
    ''' -----------------------------------------------------------------------------
    ''' <summary>
    ''' Executes a command in MySQL which has no results (insert, update, create, etc)
    ''' </summary>
    ''' <param name="sQuery">The MySQL command (properly formatted)</param>
    ''' <param name="sDataBase">Optional: To use a non-standard database</param>
    ''' <remarks>
    ''' </remarks>
    ''' <history>
    ''' 	[Jason Schreckengost]	6/25/2006	Created
    '''     [Jason Schreckengost]   6/29/2006   Changed to Sub from Function
    ''' </history>
    ''' -----------------------------------------------------------------------------
    Public Sub MySQLCommand(ByVal sQuery As String, _
      Optional ByVal sDataBase As String = "imtest")
        If Not MySQLCon Is Nothing Then
            If MySQLCon.Database <> sDataBase Then
                MySQLCon.ChangeDatabase(sDataBase)
            End If
            MySQLCom.Connection = MySQLCon
            MySQLCom.CommandText = sQuery
            MySQLCom.ExecuteNonQuery()
        Else
            If sBacklog Is Nothing Then
                sBacklog = sQuery + ";"
            Else
                sBacklog += vbNewLine + sQuery + ";"
            End If
        End If
    End Sub
#End Region
#Region "Functions"
    ''' -----------------------------------------------------------------------------
    ''' <summary>
    ''' Gets a user's current status
    ''' </summary>
    ''' <param name="sUName">The username of a user</param>
    ''' <returns>A string containing the status and the username</returns>
    ''' <remarks>
    ''' Usage: getFriendStatus(Users(x).Username)
    ''' </remarks>
    ''' <history>
    ''' 	[Jason Schreckengost]	6/23/2006	Created
    ''' </history>
    ''' -----------------------------------------------------------------------------
    Public Function getFriendStatus(ByVal sUName As String) As String
        MySQLCom.CommandText = "SELECT `logged` FROM `users` WHERE `UN`='" + sUName + "'"
        MySQLCom.Connection = MySQLCon
        Dim myReader As MySqlDataReader
        myReader = MySQLCom.ExecuteReader()
        ' Always call Read before accessing data.
        While myReader.Read()
            If myReader.Item(0) Is DBNull.Value Then
                getFriendStatus = "OFFLINE:" + sUName
            Else
                getFriendStatus = "ONLINE:" + sUName
            End If
        End While
        ' always call Close when done reading.
        myReader.Close()
        ' Close the connection when done with it.
    End Function
    ''' -----------------------------------------------------------------------------
    ''' <summary>
    ''' Retrieves the Global ID of a user
    ''' </summary>
    ''' <param name="sUsername">The username of a user</param>
    ''' <returns>ID number of user as an Integer datatype</returns>
    ''' <remarks>
    ''' For the UID of the current user, please use Users(x).UID
    ''' </remarks>
    ''' <history>
    ''' 	[Jason Schreckengost]	6/25/2006	Created
    ''' </history>
    ''' -----------------------------------------------------------------------------
    Public Function getUID(ByVal sUsername As String) As Integer
        MySQLCom.CommandText = "SELECT `UID` FROM `users` WHERE `UN`='" + sUsername + "'"
        MySQLCom.Connection = MySQLCon
        Dim myReader As MySqlDataReader
        myReader = MySQLCom.ExecuteReader()
        ' Always call Read before accessing data.
        While myReader.Read()
            getUID = myReader.Item(0)
        End While
        ' always call Close when done reading.
        myReader.Close()
        ' Close the connection when done with it.
    End Function
    ''' -----------------------------------------------------------------------------
    ''' <summary>
    ''' Returns the number of rows from the query
    ''' </summary>
    ''' <param name="sQuery">The MySQL command (properly formatted)</param>
    ''' <param name="sDatabase">Optional: To use a non-standard database</param>
    ''' <returns>Integer: Number of rows returned</returns>
    ''' <remarks>
    ''' Currently, you must place the MySQL COUNT() function into the query.
    ''' There is a current bug with the MySQL.NET Connector which does not
    ''' correctly show the number of rows returned
    ''' </remarks>
    ''' <history>
    ''' 	[Jason Schreckengost]	6/25/2006	Created
    ''' </history>
    ''' -----------------------------------------------------------------------------
    Public Function rowsQuery(ByVal sQuery As String, _
      Optional ByVal sDatabase As String = "imtest") As Integer
        If MySQLCon.Database <> sDatabase Then
            MySQLCon.ChangeDatabase(sDatabase)
        End If
        MySQLCom.CommandText = sQuery
        MySQLCom.Connection = MySQLCon
        Dim myReader As MySqlDataReader
        myReader = MySQLCom.ExecuteReader()
        ' Always call Read before accessing data.
        While myReader.Read()
            rowsQuery = myReader.Item(0)
        End While
        ' always call Close when done reading.
        myReader.Close()
        ' Close the connection when done with it.
    End Function
    ''' -----------------------------------------------------------------------------
    ''' <summary>
    ''' 
    ''' </summary>
    ''' <param name="iUID"></param>
    ''' <returns></returns>
    ''' <remarks>
    ''' </remarks>
    ''' <history>
    ''' 	[Jason Schreckengost]	6/25/2006	Created
    ''' </history>
    ''' -----------------------------------------------------------------------------
    Public Function parseFriends(ByVal iUID As Integer) As String
        If MySQLCon.Database <> "imtest" Then
            MySQLCon.ChangeDatabase("imtest")
        End If
        MySQLCom.CommandText = "SELECT UN,logged FROM users LEFT JOIN friends on friends.friend=users.UID WHERE friends.user=?UID AND friends.deleted=0"
        MySQLCom.Prepare()
        MySQLCom.Parameters.Add("?UID", iUID)
        MySQLCom.Connection = MySQLCon
        Dim myReader As MySqlDataReader
        myReader = MySQLCom.ExecuteReader()
        ' Always call Read before accessing data.
        While myReader.Read()
            parseFriends += Trim(myReader.GetString(0)) + ":"
            If myReader.Item(1) Is DBNull.Value Then
                parseFriends += "0 "
            Else
                parseFriends += "1 "
            End If
        End While
        ' always call Close when done reading.
        myReader.Close()
        ' Close the connection when done with it.
    End Function
    ''' -----------------------------------------------------------------------------
    ''' <summary>
    ''' 
    ''' </summary>
    ''' <param name="sQuery"></param>
    ''' <param name="sDatabase">Optional: To use a non-standard database</param>
    ''' <returns></returns>
    ''' <remarks>
    ''' </remarks>
    ''' <history>
    ''' 	[Jason Schreckengost]	6/25/2006	Created
    ''' </history>
    ''' -----------------------------------------------------------------------------
    Public Function getSingleColAsIntArray(ByVal sQuery As String, _
      Optional ByVal sDatabase As String = "imtest") As Object
        If MySQLCon.Database <> sDatabase Then
            MySQLCon.ChangeDatabase(sDatabase)
        End If
        Dim thisIntArray() As Integer = {-1}
        MySQLCom.CommandText = sQuery
        MySQLCom.Connection = MySQLCon
        Dim myReader As MySqlDataReader
        myReader = MySQLCom.ExecuteReader()
        ' Always call Read before accessing data.
        While myReader.Read()
            If thisIntArray(0) = -1 Then
                thisIntArray(0) = myReader.Item(0)
            Else
                ReDim Preserve thisIntArray(UBound(thisIntArray) + 1)
                If myReader(0) Is DBNull.Value Then
                    thisIntArray(thisIntArray.GetUpperBound(0)) = -1
                Else
                    thisIntArray(UBound(thisIntArray)) = myReader.Item(0)
                End If
            End If
        End While
        ' always call Close when done reading.
        myReader.Close()
        ' Close the connection when done with it.
        getSingleColAsIntArray = thisIntArray
    End Function
    ''' -----------------------------------------------------------------------------
    ''' <summary>
    ''' 
    ''' </summary>
    ''' <param name="iUID"></param>
    ''' <returns></returns>
    ''' <remarks>
    ''' </remarks>
    ''' <history>
    ''' 	[Jason Schreckengost]	6/25/2006	Created
    ''' </history>
    ''' -----------------------------------------------------------------------------
    Public Function parseGroups(ByVal iUID As Integer) As String
        If MySQLCon.Database <> "imtest" Then
            MySQLCon.ChangeDatabase("imtest")
        End If
        MySQLCom.CommandText = "SELECT group_text,group_leader from groups_members LEFT JOIN groups ON groups_members.group_id=groups.group_id WHERE UID=?UID"
        MySQLCom.Prepare()
        MySQLCom.Parameters.Add("?UID", iUID)
        MySQLCom.Connection = MySQLCon
        Dim myReader As MySqlDataReader
        myReader = MySQLCom.ExecuteReader()
        ' Always call Read before accessing data.
        parseGroups += "GROUPS:{"
        While myReader.Read()
            If parseGroups.Length > 8 Then parseGroups += ","
            parseGroups += Trim(myReader.Item(0))
            If myReader.GetInt64(1) = 1 Then parseGroups += " (L)"
        End While
        parseGroups += "}"
        ' always call Close when done reading.
        myReader.Close()
        ' Close the connection
    End Function
    ''' -----------------------------------------------------------------------------
    ''' <summary>
    ''' 
    ''' </summary>
    ''' <param name="iUID"></param>
    ''' <returns></returns>
    ''' <remarks>
    ''' </remarks>
    ''' <history>
    ''' 	[Jason Schreckengost]	6/25/2006	Created
    ''' </history>
    ''' -----------------------------------------------------------------------------
    Public Function parseOfflines(ByVal iUID As Integer) As String
        If MySQLCon.Database <> "imtest" Then
            MySQLCon.ChangeDatabase("imtest")
        End If
        MySQLCom.CommandText = "SELECT `UN`,`text`,`time` FROM `offlines` LEFT JOIN `users` ON `users`.`UID`=`offlines`.`from` WHERE `to`=?UID"
        MySQLCom.Prepare()
        MySQLCom.Parameters.Add("?UID", iUID)
        MySQLCom.Connection = MySQLCon
        Dim myReader As MySqlDataReader
        myReader = MySQLCom.ExecuteReader()
        ' Always call Read before accessing data.
        'parseOfflines += "GROUPS:{"
        While myReader.Read()
            parseOfflines += "OFFLINEFROM_" + Trim(myReader.Item("UN")) + "@" _
             + myReader.Item("time") + ":" + Len(myReader.Item("text")).ToString _
             + "{" + myReader.Item("text") + "}"
        End While
        'parseOfflines
        ' always call Close when done reading.
        myReader.Close()
        ' Close the connection
    End Function
    ''' -----------------------------------------------------------------------------
    ''' <summary>
    ''' 
    ''' </summary>
    ''' <param name="sSearchFor"></param>
    ''' <param name="iUID"></param>
    ''' <returns></returns>
    ''' <remarks>
    ''' </remarks>
    ''' <history>
    ''' 	[Jason Schreckengost]	6/25/2006	Created
    ''' </history>
    ''' -----------------------------------------------------------------------------
    Public Function parseFriendSearch(ByVal sSearchFor As String, ByVal iUID As Int32) As String
        sSearchFor = " " + sSearchFor
        Dim re As System.Text.RegularExpressions.Regex = New _
          System.Text.RegularExpressions.Regex(" [a-zA-z0-9_\.]+")
        MySQLCom.CommandText = _
            "SELECT DISTINCT(`UN`) " _
            + "FROM `users` u " _
            + "RIGHT JOIN `friends` f ON f.friend <> u.UID " _
            + "WHERE u.UID NOT IN(" _
                + "SELECT `friend` " _
                + "FROM `friends` " _
                + "WHERE `user`='?UID') " _
            + "AND u.UID!='?UID' " _
            + "AND ("
        For Each match As System.Text.RegularExpressions.Match In re.Matches(sSearchFor)
            MySQLCom.CommandText += " `UN` LIKE '%" + match.ToString.Trim + "%' OR"
        Next
        MySQLCom.CommandText = MySQLCom.CommandText.Substring(0, MySQLCom.CommandText.Length - 3) + ")"
        Debug.WriteLine(MySQLCom.CommandText)
        MsgBox(MySQLCom.CommandText)
        MySQLCom.Prepare()
        MySQLCom.Parameters.Add("?UID", iUID.ToString)
        MySQLCom.Connection = MySQLCon
        Dim myReader As MySqlDataReader
        myReader = MySQLCom.ExecuteReader()
        ' Always call Read before accessing data.
        parseFriendSearch = "FRIENDRESULT_"
        While myReader.Read()
            If parseFriendSearch <> "FRIENDRESULT_" Then parseFriendSearch += ":"
            parseFriendSearch += Trim(myReader.Item("UN"))
        End While
        'parseOfflines
        ' always call Close when done reading.
        myReader.Close()
    End Function
    ''' -----------------------------------------------------------------------------
    ''' <summary>
    ''' 
    ''' </summary>
    ''' <param name="User"></param>
    ''' <param name="thisStatus"></param>
    ''' <remarks>
    ''' </remarks>
    ''' <history>
    ''' 	[Jason Schreckengost]	6/25/2006	Created
    ''' </history>
    ''' -----------------------------------------------------------------------------
    Public Sub friendBroadcast(ByVal User As clsUser, ByVal thisStatus As clsUser.eStatus)
        Dim msgToSend As String
        Select Case thisStatus
            Case User.eStatus.Offline
                msgToSend = "OFFLINE:"
            Case User.eStatus.Online
                msgToSend = "ONLINE:"
            Case User.eStatus.Away
            Case User.eStatus.BeRightBack
            Case User.eStatus.Busy
            Case User.eStatus.DoNotDisturb
            Case User.eStatus.Invisible
            Case Else
                msgToSend = "CUSTOM:" + User.Message + "_"
        End Select
        For Each x As Integer In getSingleColAsIntArray _
          ("SELECT logged FROM users LEFT JOIN friends ON users.UID" _
          + "=friends.user WHERE friend=" + User.UID.ToString _
          + " AND logged is not null")
            'Send the trigger that the client needs to update the contacts
            If x <> -1 Then Users(x).Send(msgToSend + User.Username)
        Next 'Loop again
    End Sub
#End Region
    Public ReadOnly Property Connected() As Boolean
        Get
            Connected = IIf(Not MySQLCon.State().Closed, True, False)
        End Get
    End Property
    Private Sub getMySQLCon()
        If Threading.Thread.CurrentThread.Name Is Nothing Then _
          Threading.Thread.CurrentThread.Name = "clsMySQL.getMySQLCon"
        Do Until Not MySQLCon Is Nothing
            Try
                MySQLCon.Open()
            Catch ex As Exception
                'really, do nothing, just loop until its done
                'MySQLCon.Close()
                'MySQLCon.Dispose()
                MySQLCon = Nothing
            End Try
        Loop
        Dim dlg As New oneStrParamDlg(AddressOf Display)
        Dim args() As Object = {"User #" + userId + " now has a MySQL Connection." + vbNewLine + "Flushing backlog."}
        frmServer.Invoke(dlg, args)
        Me.MySQLCommand(sBacklog)
    End Sub
    Private Delegate Sub oneStrParamDlg(ByVal data As String)
End Class